-- enable FILESTEAM on the SQL Server instance
EXEC sp_filestream_configure 
  @enable_level = 3
, @share_name = N'FS';

-- disable
EXEC sp_filestream_configure @enable_level = 0;

-- add filegroup with CONTAINS FILESTEAM
ALTER DATABASE fs
ADD FILEGROUP fs_fg_filestream CONTAINS FILESTREAM
GO

ALTER DATABASE fs
ADD FILE
(
    NAME= 'fs_filestream',
    FILENAME = 'C:\db\fs'
)
TO FILEGROUP fs_fg_filestream
GO


SELECT 
 SERVERPROPERTY ('FilestreamShareName') ShareName
,SERVERPROPERTY ('FilestreamConfiguredLevel') ConfiguredLevel
,SERVERPROPERTY ('FilestreamEffectiveLevel') EffectiveLevel


--DROP TABLE dbo.BLOB

CREATE TABLE dbo.BLOB (
    ID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
    BLOB VARBINARY(MAX) FILESTREAM NULL
)


DECLARE @ID UNIQUEIDENTIFIER
SET @ID = NEWID()

INSERT INTO dbo.BLOB
 (ID, BLOB)
VALUES 
 (@ID, CAST('BLOB' AS VARBINARY(MAX)))

SELECT ID, BLOB 
FROM dbo.BLOB
WHERE ID = @ID

SELECT BLOB.PathName() 
FROM dbo.BLOB
WHERE ID = @ID

-DROP PROCEDURE dbo.stp_AddBLOB


-- Add a row with an empty BLOB
CREATE PROCEDURE dbo.stp_AddBLOB
AS
BEGIN
    DECLARE @ID UNIQUEIDENTIFIER
    SET @ID = NEWID()

    INSERT INTO dbo.BLOB
    (ID, BLOB)
    VALUES 
    (@ID, CAST('' AS VARBINARY(MAX)))

    SELECT ID, BLOB.PathName()
    FROM dbo.BLOB
    WHERE ID = @ID
END


CREATE PROCEDURE dbo.stp_GetBLOB
@ID UNIQUEIDENTIFIER
AS
BEGIN
    SELECT BLOB.PathName()
    FROM dbo.BLOB
    WHERE ID = @ID
END


EXEC dbo.stp_AddBLOB


SELECT ID, BLOB, BLOB.PathName() 
FROM dbo.BLOB


